﻿/*******************************************************************************
 * Copyright © 2016 NFine.Framework 版权所有
 * Author: NFine
 * Description: NFine快速开发平台
 * Website：http://www.nfine.cn
*********************************************************************************/
using NFine.Application.SystemSecurity;
using NFine.Code;
using NFine.Domain._03_Entity.SystemSecurity;
using NFine.Domain.Entity.SystemSecurity;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Web;
using System.Web.Mvc;

namespace NFine.Web.Areas.SystemSecurity.Controllers
{
    public class BridgeMangeController : ControllerBase
    {
        static string connectionString = "Server=DESKTOP-V32GFNF;Database=NFineBase;User Id=sa;Password=123456;Encrypt=False;";

        private BridgeDataApp bridgeDataApp = new BridgeDataApp();
        [HttpGet]
        [HandlerAjaxOnly]
        public ActionResult GetGridJson(string queryJson)
        {
            List<BridgeDataEntity> bridgeDataList = new List<BridgeDataEntity>();
            string query = $"SELECT bm.[Bridgetype],bm.[Bridgecode],bm.[Centercode],bm.[Routecode],bm.[Routename],bm.[Routelevel],bm.[Bridgelength],bm.[SpanLength],bm.[MaxSpan],bm.[BirdgeWidth],bm.[BridgeUnitWidth],bm.[SpanUnit],bm.[CenterBridgeType],bm.[CenterBridgeMaterial],bm.[BridgePierType],bm.[LoadLevel],bm.[CrossLandType],COALESCE(CONVERT(VARCHAR(23), bc.CheckTime, 120),null) AS CheckTime,COALESCE(CONVERT(VARCHAR(23), bc.CheckNextTime, 120),null) AS CheckNextTime,bm.[KyueLandType],bm.[CheckPort],bm.[DjProcedureCompany] FROM [NFineBase].[dbo].[Sys_Bridgemanage] bm OUTER APPLY (SELECT TOP 1 CheckTime,CheckNextTime FROM [NFineBase].[dbo].[Sys_BridgeCheck] bc_sub WHERE bc_sub.Bridgecode = bm.Bridgecode ORDER BY CheckTime DESC) bc;";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(query, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    bridgeDataList.Add(new BridgeDataEntity
                    {
                        Bridgetype = reader.GetString(0),
                        Bridgecode = reader.GetString(1),
                        Centercode = reader.GetString(2),
                        Routecode = reader.GetString(3),
                        Routename = reader.GetString(4),
                        Routelevel = reader.GetString(5),
                        Bridgelength = reader.GetString(6),
                        SpanLength = reader.GetString(7),
                        MaxSpan = reader.GetString(8),
                        BirdgeWidth = reader.GetString(9),
                        BridgeUnitWidth = reader.GetString(10),
                        SpanUnit = reader.GetString(11),
                        CenterBridgeType = reader.GetString(12),
                        CenterBridgeMaterial = reader.GetString(13),
                        BridgePierType = reader.GetString(14),
                        LoadLevel = reader.GetString(15),
                        CrossLandType = reader.GetString(16),
                        CheckTime = reader.IsDBNull(17) ? (DateTime?)null : reader.GetString(17).ToDate(),
                        CheckNextTime = reader.IsDBNull(18) ? (DateTime?)null : reader.GetString(18).ToDate(),
                        KyueLandType = reader.IsDBNull(19) ? null : reader.GetString(19),
                        DjProcedureCompany = reader.IsDBNull(20) ? null : reader.GetString(20),
                        CheckPort = reader.IsDBNull(21) ? null : reader.GetString(21)

                    });
                }
            }


            return Content(bridgeDataList.ToJson());
        }

        [HttpPost]
        [HandlerAjaxOnly]
        public ActionResult UpdateBrigdeData11(
            string queryJson,
            string bridgeCode,
            string RouteCode,
            string DJprocedure,
            HttpPostedFileBase Djport)  // 关键修正点1：文件参数类型:ml-citation{ref="1,2" data="citationList"}
        {
            // 1. 参数校验
            if (Djport == null || Djport.ContentLength == 0)
            {
                return Content(new { code = 500, msg = "文件不能为空" }.ToJson());
            }

            // 2. 文件保存逻辑
            var serverPath = Server.MapPath("~/UploadFiles/");
            var fileName = $"{Path.GetFileName(Djport.FileName)}";
            var fullPath = Path.Combine(serverPath, fileName);
            Djport.SaveAs(fullPath);  // 关键修正点2：保存文件:ml-citation{ref="5" data="citationList"}

            // 3. 参数化SQL防注入:ml-citation{ref="5" data="citationList"}
            const string query = @"
        INSERT INTO Sys_BridgeCheck 
        (CheckTime, DjProcedureCompany, CheckPort, CheckNextTime, Bridgecode, RouteCode) 
        VALUES 
        (@CheckTime, @DJprocedure, @CheckPort, 
         CASE WHEN @RouteCode = 'level1' THEN DATEADD(MONTH, 1, @CheckTime)
              WHEN @RouteCode = 'level2' THEN DATEADD(MONTH, 6, @CheckTime)
              WHEN @RouteCode = 'level3' THEN DATEADD(YEAR, 1, @CheckTime) END,
         @bridgeCode, @RouteCode)";

            using (var connection = new SqlConnection(connectionString))
            {
                var command = new SqlCommand(query, connection);
                command.Parameters.AddRange(new[] {
            new SqlParameter("@CheckTime", queryJson),
            new SqlParameter("@DJprocedure", DJprocedure),
            new SqlParameter("@CheckPort", fullPath),  // 存储文件路径:ml-citation{ref="5" data="citationList"}
            new SqlParameter("@RouteCode", RouteCode),
            new SqlParameter("@bridgeCode", bridgeCode)
        });
                connection.Open();
                command.ExecuteNonQuery();
            }

            return Content(new { code = 200 }.ToJson());
        }
        [HttpPost]
        [HandlerAjaxOnly]
        public ActionResult UpdateBrigdeData(string queryJson, string bridgeCode, string RouteCode, string DJprocedure, string Djport)
        {

            List<BridgeDataEntity> bridgeDataList = new List<BridgeDataEntity>();
            string query = $"INSERT INTO Sys_BridgeCheck (CheckTime,DjProcedureCompany,CheckPort,CheckNextTime,Bridgecode,RouteCode)VALUES ('{queryJson}','{DJprocedure}','{Djport}',CASE WHEN '{RouteCode} '= 'level1' THEN DATEADD(MONTH, 1, '{queryJson}')WHEN '{RouteCode}' = 'level2' THEN DATEADD(MONTH, 6, '{queryJson}')WHEN '{RouteCode}' = 'level3' THEN DATEADD(YEAR, 1,'{queryJson}')END,'{bridgeCode}','{RouteCode}');";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(query, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
            }
            var result = GeBridgeDataPort(bridgeCode);


            return Content(result.ToJson());
        }
        public ActionResult SelectBridgeData(string bridgeCode)
        {

            List<BridgeDataEntity> bridgeDataList = new List<BridgeDataEntity>();
            string query = $"SELECT [CheckTime],[CheckNextTime] from Sys_Bridgemanage WHERE Bridgecode ='{bridgeCode}'";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(query, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    bridgeDataList.Add(new BridgeDataEntity
                    {
                        Bridgecode = bridgeCode,
                        CheckTime = reader.IsDBNull(0) ? (DateTime?)null : reader.GetDateTime(0),
                        CheckNextTime = reader.IsDBNull(1) ? (DateTime?)null : reader.GetDateTime(1),
                    });

                }

                return Content(bridgeDataList.ToJson());
            }
        }
        [HttpGet]
        [HandlerAjaxOnly]
        public ActionResult GeBridgeDataCompany(string keyValue, int rows, int page, string sidx, string sord)
        {
            List<BridgeCheck> bridgeDataList = new List<BridgeCheck>();
            string query = $"SELECT bm.Bridgetype,bc.Bridgecode,bc.CheckTime,bc.CheckNextTime,bc.CheckPort,bc.PushStatus,bc.DjProcedureCompany FROM Sys_BridgeCheck bc LEFT JOIN Sys_Bridgemanage bm ON bc.Bridgecode=bm.Bridgecode WHERE bc.BridgeCode = '{keyValue}';";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(query, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    bridgeDataList.Add(new BridgeCheck
                    {
                        Bridgetype = reader.GetString(0),
                        Bridgecode = reader.GetString(1),
                        CheckTime = reader.IsDBNull(2) ? (DateTime?)null : reader.GetDateTime(2),
                        CheckNextTime = reader.IsDBNull(3) ? (DateTime?)null : reader.GetDateTime(3),
                        CheckPort = reader.IsDBNull(4) ? "无报告" : reader.GetString(4), // 修改这里
                        CheckStatus = reader.IsDBNull(5) ? "未推送" : reader.GetString(5),
                        DjProcedureCompany = reader.IsDBNull(6) ? null : reader.GetString(6),
                    });
                }
            }


            return Content(bridgeDataList.ToJson());
        }


        [HttpGet]
        [HandlerAjaxOnly]
        public ActionResult GeBridgeDataPort(string queryJson)
        {
            List<BridgeCheck> bridgeDataList = new List<BridgeCheck>();
            string query = $"SELECT [Bridgetype],[Bridgecode],[CheckTime],[CheckNextTime],[CheckPort],[PushStatus],[DjProcedureCompany] FROM [NFineBase].[dbo].[Sys_BridgeCheck] where Bridgecode='{queryJson}'";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(query, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    bridgeDataList.Add(new BridgeCheck
                    {
                        Bridgetype = reader.GetString(0),
                        Bridgecode = reader.GetString(1),
                        CheckTime = reader.IsDBNull(2) ? (DateTime?)null : reader.GetDateTime(2),
                        CheckNextTime = reader.IsDBNull(3) ? (DateTime?)null : reader.GetDateTime(3),
                        CheckPort = reader.IsDBNull(4) ? "无报告" : reader.GetString(4), // 修改这里
                        CheckStatus = reader.IsDBNull(5) ? "未推送" : reader.GetString(5),
                        DjProcedureCompany = reader.IsDBNull(6) ? null : reader.GetString(6),
                    });
                }
            }


            return Content(bridgeDataList.ToJson());

        }
        [HttpGet]
        [HandlerAjaxOnly]
        public ActionResult GeDiseaseDataCompany(string keyValue, int rows, int page, string sidx, string sord)
        {

            List<DiseaseMan> DiseaseManList = new List<DiseaseMan>();
            string query = $"SELECT bm.Bridgetype,bc.Bridgecode,bc.EntryTime,bc.Diseasetype,bc.DiseaseDescription,bc.Score,bc.DiseaseImg,bc.IsDIseaseImp,bc.ImpDiseaseType FROM Sys_DiseaseMan bc LEFT JOIN Sys_Bridgemanage bm ON bc.Bridgecode=bm.Bridgecode WHERE bc.BridgeCode = '{keyValue}';";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(query, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    DiseaseManList.Add(new DiseaseMan
                    {
                        Bridgetype = reader.GetString(0),
                        Bridgecode = reader.GetString(1),
                        EntryTime = reader.IsDBNull(2) ? (DateTime?)null : reader.GetDateTime(2),
                        Diseasetype = reader.IsDBNull(3) ? null : reader.GetString(3),
                        DiseaseDescription = reader.IsDBNull(4) ? "无报告" : reader.GetString(4), // 修改这里
                        Score = reader.IsDBNull(5) ? "无报告" : reader.GetString(5),
                        DiseaseImg = reader.IsDBNull(6) ? null : reader.GetString(6),
                        IsDIseaseImp = reader.IsDBNull(7) ? null : reader.GetString(7),
                        ImpDiseaseType = reader.IsDBNull(8) ? null : reader.GetString(8),
                    });
                }
            }


            return Content(DiseaseManList.ToJson());
        }


        [HttpPost]
        [HandlerAjaxOnly]
        public ActionResult UpdateDiseaseData11(string bridgeCode,  string RouteCode,string BingHaiType, string BingHaiDes, string IsImport, string score,string BinghaiSelect,HttpPostedFileBase Img)  // 关键修正点1：文件参数类型:ml-citation{ref="1,2" data="citationList"}
        {
            // 1. 参数校验
            if (Img == null || Img.ContentLength == 0)
            {
                return Content(new { code = 500, msg = "文件不能为空" }.ToJson());
            }

            // 2. 文件保存逻辑
            var serverPath = Server.MapPath("~/UploadFiles/");
            var fileName = $"{Path.GetFileName(Img.FileName)}";
            var fullPath = Path.Combine(serverPath, fileName);
            Img.SaveAs(fullPath);  // 关键修正点2：保存文件:ml-citation{ref="5" data="citationList"}

            // 3. 参数化SQL防注入:ml-citation{ref="5" data="citationList"}
            const string query = @"
        INSERT INTO Sys_DiseaseMan 
        (Bridgecode, Diseasetype, DiseaseDescription, Score, DiseaseImg, IsDIseaseImp, ImpDiseaseType)
        VALUES 
        (@bridgeCode, @RouteCode, @BingHaiDes,@score,@Img,
         @IsImport, @BinghaiSelect)";

            using (var connection = new SqlConnection(connectionString))
            {
                var command = new SqlCommand(query, connection);
                command.Parameters.AddRange(new[] {
            new SqlParameter("@Img", fullPath),  // 存储文件路径:ml-citation{ref="5" data="citationList"}
            new SqlParameter("@RouteCode", RouteCode),
            new SqlParameter("@bridgeCode", bridgeCode),
             new SqlParameter("@BingHaiDes", BingHaiDes),  // 存储文件路径:ml-citation{ref="5" data="citationList"}
            new SqlParameter("@BinghaiSelect", BinghaiSelect),
            new SqlParameter("@score", score),
              new SqlParameter("@IsImport", IsImport)
        });
                connection.Open();
                command.ExecuteNonQuery();
            }

            return Content(new { code = 200 }.ToJson());
        }


        [HttpGet]
        [HandlerAjaxOnly]
        public ActionResult GetChartDataFromDatabase()
        {
            List<BridgeCheckCount> bridgeDataList = new List<BridgeCheckCount>();
            string query = $"SELECT ImpDiseaseType, COUNT(*) AS Count FROM Sys_DiseaseMan WHERE ImpDiseaseType IS NOT NULL GROUP BY ImpDiseaseType ORDER BY Count DESC;";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(query, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    bridgeDataList.Add(new BridgeCheckCount
                    {
                        CountType = reader.IsDBNull(0) ? "" : reader.GetString(0),
                        Count2 = reader.GetSqlInt32(1).ToInt(),
                    });
                }
            }
            return Content(bridgeDataList.ToJson());

        }
    }
}
